﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CRMSystemV2.DBUtility;
using CRMSystemV2.IDAL;
namespace CRMSystemV2.SQLServerDAL
{
    public class User : DALBase, IUser
    {
        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public string Login(string username, string password)
        {
            SqlParameter[] pars = new SqlParameter[] { 
                new SqlParameter("@ip_username",SqlDbType.VarChar,50),
                new SqlParameter("@ip_password",SqlDbType.VarChar,50),
                new SqlParameter("@op_returnvalue",SqlDbType.VarChar,500)
            };
            pars[0].Value = username;
            pars[1].Value = password;
            pars[2].Direction = ParameterDirection.Output;
            SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.StoredProcedure, "up_user_login", pars);
            return pars[2].Value.ToString();
        }
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="username"></param>
        /// <param name="oldpassword"></param>
        /// <param name="newpassword"></param>
        /// <returns></returns>
        public string ChangePassword(string username, string oldpassword, string newpassword)
        {
            SqlParameter[] pars = new SqlParameter[] { 
                new SqlParameter("@ip_username",SqlDbType.VarChar,50),
                new SqlParameter("@ip_oldpassword",SqlDbType.VarChar,50),
                new SqlParameter("@ip_newpassword",SqlDbType.VarChar,50),
                new SqlParameter("@op_returnvalue",SqlDbType.VarChar,500)
            };
            pars[0].Value = username;
            pars[1].Value = oldpassword;
            pars[2].Value = newpassword;
            pars[3].Direction = ParameterDirection.Output;
            SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.StoredProcedure, "up_user_changepassword", pars);
            return pars[3].Value.ToString();
        }
        /// <summary>
        /// 分页获取列表
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="RowCount"></param>
        /// <param name="PageCount"></param>
        /// <returns></returns>
        public DataTable GetPageView(int pageSize, int pageIndex, Dictionary<string, string> keyWords, string orderField, string orderDirection)
        {
            StringBuilder strSQL = new StringBuilder("select [id],[username],[jobno],[email],[phone],[isenable] from dbo.[user]");
            //拼接条件查询
            string strWhere = GetStringWhere(keyWords);
            if (!string.IsNullOrEmpty(strWhere))
                strSQL.AppendFormat(" where {0}", strWhere);
            //拼接排序
            strSQL.Append(base.GetStringOrderby(orderField, orderDirection));
            return GetPageView(pageSize, pageIndex, strSQL.ToString());
        }
        /// <summary>
        /// 获取导出数据表
        /// </summary>
        /// <param name="topCount"></param>
        /// <param name="orderField"></param>
        /// <param name="orderDirection"></param>
        /// <returns></returns>
        public DataTable GetExportTable(int topCount, Dictionary<string, string> keyWords, string orderField, string orderDirection)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendFormat("select top {0} [id],[username],[jobno],[email],[phone],[isenable]=(CASE [isenable] WHEN 1 THEN '是' ELSE '否' END) from dbo.[user]", topCount);

            //拼接条件查询
            string strWhere = GetStringWhere(keyWords);
            if (!string.IsNullOrEmpty(strWhere))
                strSQL.AppendFormat(" where {0}", strWhere);
            //拼接排序
            strSQL.Append(base.GetStringOrderby(orderField, orderDirection));
            return SQLHelper.Query(strSQL.ToString(), SQLHelper.ConnectionString).Tables[0];
        }

        /// <summary>
        /// 重置密码,默认密码123456，以后可考虑加密
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public string ResetPassword(string id, string password)
        {
            SqlParameter[] pars = new SqlParameter[] { 
                new SqlParameter("@ip_id",SqlDbType.Int),
                new SqlParameter("@ip_password",SqlDbType.VarChar,50),                
                new SqlParameter("@op_returnvalue",SqlDbType.VarChar,500)
            };
            pars[0].Value = id;
            pars[1].Value = password;
            pars[2].Direction = ParameterDirection.Output;
            SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.StoredProcedure, "up_user_resetpassword", pars);
            return pars[2].Value.ToString();
        }
        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public string Insert(Model.User model)
        {
            SqlParameter[] pars = new SqlParameter[] { 
                new SqlParameter("@ip_username",SqlDbType.VarChar,50),
                new SqlParameter("@ip_jobno",SqlDbType.VarChar,50),
                new SqlParameter("@ip_email",SqlDbType.VarChar,50),
                new SqlParameter("@ip_phone",SqlDbType.VarChar,50),
                new SqlParameter("@ip_isenable",SqlDbType.Bit),
                new SqlParameter("@op_returnvalue",SqlDbType.VarChar,500)
            };
            pars[0].Value = model.UserName;
            pars[1].Value = model.JobNO;
            pars[2].Value = model.Email;
            pars[3].Value = model.Phone;
            pars[4].Value = model.IsEnable;
            pars[5].Direction = ParameterDirection.Output;
            SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.StoredProcedure, "up_user_insert", pars);
            return pars[5].Value.ToString();
        }
        /// <summary>
        /// 删除用户，支持批量删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string Delete(string ids)
        {
            SqlParameter[] pars = new SqlParameter[] { 
                new SqlParameter("@ip_ids",SqlDbType.VarChar,200),                            
                new SqlParameter("@op_returnvalue",SqlDbType.VarChar,500)
            };
            pars[0].Value = ids;
            pars[1].Direction = ParameterDirection.Output;
            SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.StoredProcedure, "up_user_delete", pars);
            return pars[1].Value.ToString();
        }        
        /// <summary>
        /// 修改用户资料
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public string Update(Model.User model)
        {
            SqlParameter[] pars = new SqlParameter[] { 
                new SqlParameter("@ip_id",SqlDbType.Int),
                new SqlParameter("@ip_jobno",SqlDbType.VarChar,50),
                new SqlParameter("@ip_email",SqlDbType.VarChar,50),
                new SqlParameter("@ip_phone",SqlDbType.VarChar,50),
                new SqlParameter("@ip_isenable",SqlDbType.Bit),
                new SqlParameter("@op_returnvalue",SqlDbType.VarChar,500)
            };
            pars[0].Value = model.ID;
            pars[1].Value = model.JobNO;
            pars[2].Value = model.Email;
            pars[3].Value = model.Phone;
            pars[4].Value = model.IsEnable;
            pars[5].Direction = ParameterDirection.Output;
            SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.StoredProcedure, "up_user_update", pars);
            return pars[5].Value.ToString();
        }
        /// <summary>
        /// 查看详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Model.User GetModel(string id)
        {
            SqlParameter[] pars = new SqlParameter[] { 
                new SqlParameter("@ip_id",SqlDbType.Int)
            };
            pars[0].Value = id;
            using (IDataReader dataReader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.StoredProcedure, "up_user_detail", pars))
            {
                while (dataReader.Read())
                {
                    return ReaderBind(dataReader);
                }
            }
            return null;
        }
        /// <summary>
        /// 对象实体绑定数据
        /// </summary>
        private Model.User ReaderBind(IDataReader dataReader)
        {
            Model.User model = new Model.User();
            object ojb;
            ojb = dataReader["id"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.ID = (int)ojb;
            }
            model.UserName = dataReader["username"].ToString();
            model.JobNO = dataReader["jobno"].ToString();
            model.Email = dataReader["email"].ToString();
            model.Phone = dataReader["phone"].ToString();
            ojb = dataReader["isenable"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.IsEnable = (bool)ojb;
            }
            return model;
        }
        protected override string GetStringWhere(Dictionary<string, string> keyWords)
        {
            if (keyWords.Count == 0)
                return string.Empty;
            StringBuilder sbWhere = CreateStringBuilder();
            if (!string.IsNullOrEmpty(keyWords["username"]))
                sbWhere.AppendFormat(" and [username] like '%{0}%'", keyWords["username"]);
            if (!string.IsNullOrEmpty(keyWords["jobno"]))
                sbWhere.AppendFormat(" and [jobno]='{0}'", keyWords["jobno"]);
            if (!string.IsNullOrEmpty(keyWords["email"]))
                sbWhere.AppendFormat(" and [email]='{0}'", keyWords["email"]);
            if (!string.IsNullOrEmpty(keyWords["phone"]))
                sbWhere.AppendFormat(" and [phone]='{0}'", keyWords["phone"]);
            if (!string.IsNullOrEmpty(keyWords["isenable"]))
                sbWhere.AppendFormat(" and [isenable]={0}", keyWords["isenable"]);

            return GetReplacedWhere(sbWhere);
        }
    }
}
